﻿--------------------------------------------------------
--  DDL for Table ANNOUNCEMENT
--------------------------------------------------------
CREATE TABLE PADMIN.ANNOUNCEMENT
(
ID NUMBER CONSTRAINT PK_ANNOUNCEMENT PRIMARY KEY, 
JOBID NUMBER,
TITLE VARCHAR2(200 BYTE) , 
BEGINTIME DATE , 
ENDTIME DATE, 
CREATEBY NUMBER, 
CREATETIME DATE, 
CONTENT VARCHAR2(4000 BYTE), 
INVALID NUMBER 
);
   
  
CREATE SEQUENCE seq_ANNOUNCEMENT;
CREATE OR REPLACE TRIGGER PADMIN.TRI_ANNOUNCEMENT 
before insert on ANNOUNCEMENT
for each row
begin
select seq_ANNOUNCEMENT.NEXTVAL
into :new.ID
from dual;
end;


--------------------------------------------------------
--  DDL for Table CYCLE   EPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY
--------------------------------------------------------

CREATE TABLE PADMIN.CYCLE 
(
ID NUMBER CONSTRAINT PK_CYCLE PRIMARY KEY, 
JOBID NUMBER, 
PLANTYPE NUMBER, 
BEGINDATE DATE, 
ENDDATE DATE, 
ISLENGTHVALID NUMBER , 
LENGTH NUMBER, 
HOLIDAYINVALID NUMBER , 
MONTH NUMBER, 
DAY NUMBER, 
CREATEDATETIME DATE, 
INVALID NUMBER 
) ;
 
CREATE SEQUENCE seq_cycle;

CREATE OR REPLACE TRIGGER PADMIN.TRI_CYCLE 
before insert on cycle
for each row
begin
select seq_cycle.NEXTVAL
into :new.ID
from dual;
end;


--------------------------------------------------------
--  DDL for Table JOB_X
--------------------------------------------------------

CREATE TABLE PADMIN.JOB_X 
(
ID NUMBER CONSTRAINT PK_JOB_X PRIMARY KEY, 
JOBNAME VARCHAR2(100 BYTE), 
JOBDESCRIPTION VARCHAR2(500 BYTE), 
JOBTYPE NUMBER, 
JOBOWNERID NUMBER, 
TASKUSERID NUMBER, 
THIRDTYPEID NUMBER, 
THIRDTYPENAME VARCHAR2(100 BYTE), 
JOBDETAIL VARCHAR2(4000 BYTE), 
JOBRANGE VARCHAR2(200 BYTE), 
HASBULLETIN NUMBER , 
CREATETIME DATE, 
UPDATETIME DATE, 
INVALID NUMBER 
) ;
   
CREATE SEQUENCE seq_job;

CREATE OR REPLACE TRIGGER PADMIN.TRI_JOB 
before insert on JOB_X
for each row
begin
select seq_job.NEXTVAL
into :new.ID
from dual;
end;



--------------------------------------------------------
--  DDL for Table INSPECTIONTYPE
--------------------------------------------------------

CREATE TABLE PADMIN.INSPECTIONTYPE 
(
ID NUMBER CONSTRAINT PK_INSPECTIONTYPE PRIMARY KEY, 
CODE VARCHAR2(20 BYTE), 
NAME VARCHAR2(200 BYTE), 
CREATETIME DATE, 
CREATORID NUMBER, 
INVALID NUMBER 
);
   
CREATE SEQUENCE seq_INSPECTIONTYPE;

CREATE OR REPLACE TRIGGER PADMIN.TRI_INSPECTIONTYPE 
before insert on INSPECTIONTYPE
for each row
begin
select seq_INSPECTIONTYPE.NEXTVAL
into :new.ID
from dual;
end;

--------------------------------------------------------
--  DDL for Table TASK
--------------------------------------------------------

CREATE TABLE PADMIN.TASK 
(
ID NUMBER CONSTRAINT PK_TASK PRIMARY KEY, 
JOBNO VARCHAR2(50 BYTE), 
JOBID NUMBER, 
CYCLEID NUMBER, 	
TASKNAME VARCHAR2(500 BYTE), 
TaskDescription VARCHAR2(2000 BYTE),
TaskDetail VARCHAR2(4000 BYTE),	
STATUS NUMBER , 
LOCATION NUMBER, 
JOBTYPE NUMBER, 
BEGINDATE DATE, 
ENDDATE DATE, 
OwnerID NUMBER, 
CREATEUSERID NUMBER, 
UPDATEUSERID NUMBER, 
CREATETIME DATE, 
UPDATETIME DATE, 
COMPLETETIME DATE, 
OVERFLAG NUMBER , 
REMARK VARCHAR2(4000 BYTE)
);
   
CREATE SEQUENCE seq_task;

CREATE OR REPLACE TRIGGER PADMIN.TRI_TASK 
before insert on Task
for each row
begin
select seq_task.NEXTVAL
into :new.ID
from dual;
end;
　
--------------------------------------------------------
--  DDL for Table TASKLOG
--------------------------------------------------------

CREATE TABLE PADMIN.TASKLOG 
(
ID NUMBER CONSTRAINT PK_TASKLOG PRIMARY KEY, 
TASKID NUMBER, 
JOBNO VARCHAR2(50 BYTE), 
JOBID NUMBER, 
CYCLEID NUMBER, 	
TASKNAME VARCHAR2(500 BYTE), 
TaskDescription VARCHAR2(2000 BYTE),
TaskDetail VARCHAR2(4000 BYTE),	
STATUS NUMBER , 
LOCATION NUMBER, 
JOBTYPE NUMBER, 
BEGINDATE DATE, 
ENDDATE DATE, 
OwnerID NUMBER, 
CREATEUSERID NUMBER, 
UPDATEUSERID NUMBER, 
CREATETIME DATE, 
UPDATETIME DATE, 
COMPLETETIME DATE, 
OVERFLAG NUMBER , 
REMARK VARCHAR2(4000 BYTE)
) ;
   
   

CREATE SEQUENCE seq_taskLog;

CREATE OR REPLACE TRIGGER PADMIN.TRI_TASKLOG 
before insert on TaskLog
for each row
begin
select seq_taskLog.NEXTVAL
into :new.ID
from dual;
end;
　

--------------------------------------------------------
--  DDL for Table TASKATTACHMENT
--------------------------------------------------------

CREATE TABLE PADMIN.TASKATTACHMENT 
(
ID NUMBER CONSTRAINT PK_TASKATTACHMENT PRIMARY KEY, 
TASKID NUMBER, 
FILENAME VARCHAR2(200 BYTE), 
UPLOADTIME DATE, 
UPLOADPATH VARCHAR2(200 BYTE), 
UPLOADUSERID NUMBER
) ;
   
CREATE SEQUENCE seq_TASKATTACHMENT ;

CREATE OR REPLACE TRIGGER PADMIN.TRI_TASKATTACHMENT 
before insert on TASKATTACHMENT
for each row
begin
select seq_TASKATTACHMENT.NEXTVAL
into :new.ID
from dual;
end;
------------------------------------------------------------------
   
CREATE TABLE PADMIN.PlanTaskScope 
(
ID NUMBER CONSTRAINT PK_PlanTaskScope PRIMARY KEY, 
CYCLEID NUMBER, 	
LocationID NUMBER 
) ;

CREATE SEQUENCE seq_PlanTaskScope;

CREATE OR REPLACE TRIGGER PADMIN.TRI_PlanTaskScope 
before insert on PlanTaskScope
for each row
begin
select seq_PlanTaskScope.NEXTVAL
into :new.ID
from dual;
end;
　

-------------------------------------------------------------
CREATE TABLE PADMIN.PlanLocationOwner 
(
	ID NUMBER CONSTRAINT PK_PlanLocationOwner PRIMARY KEY, 
	LOCATIONID NUMBER,
	ALIAS VARCHAR2(200 BYTE),
	OwnerID NUMBER, 	
	OwnerIDbak NUMBER 
) ;
CREATE SEQUENCE seq_PlanLocationOwner;

CREATE OR REPLACE TRIGGER PADMIN.TRI_PlanLocationOwner 
before insert on PlanLocationOwner
for each row
begin
select seq_PlanLocationOwner.NEXTVAL
into :new.ID
from dual;
end;

insert into PLANLOCATIONOWNER(LOCATIONID,alias,OwnerID,owneridbak)
select id,alias,33362,33362 from sitebranch b where not exists(select * from PLANLOCATIONOWNER a where LOCATIONNAMEID=b.id);
commit;




--任务短信表
CREATE TABLE "PADMIN"."SMSTASK"
  (
    "ID"         NUMBER NOT NULL ENABLE,
    "TASKID"     NUMBER NOT NULL ENABLE,
    "JOBNO"      VARCHAR2(50 BYTE),
    "JOBID"      NUMBER,
    "CYCLEID"    NUMBER,
    "TASKNAME"   VARCHAR2(200 BYTE),
    "STATUS"     NUMBER NOT NULL ENABLE,
    "OWNERID"    NUMBER,
    "SMSCONTENT" VARCHAR2(2000 BYTE),
    "CREATETYPE" NUMBER,
    "CREATETIME" DATE,
    "UPDATETIME" DATE,
    "TELNUMBER" VARCHAR2(50 BYTE),
    "FROM_USER" NUMBER,
    "TO_USER"   NUMBER,
    CONSTRAINT "SMSTASK_PK" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PUNIFY" ENABLE
  )
  TABLESPACE "PUNIFY" ;

  CREATE SEQUENCE sql_SMSTask;
CREATE OR REPLACE TRIGGER "PADMIN"."TG_SMSTASK" before
  INSERT ON SMSTask FOR EACH row BEGIN
  SELECT sql_SMSTask.NEXTVAL INTO :new.ID FROM dual;
END;
------------------------------------------------------------------------------------------